package com.xinkao.example.util.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.Table;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.fastjson.JSON;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class EasyExcelUtil {
    /**
     * 读取某个 sheet 的 Excel
     * @param excel    文件
     * @param clazz 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     */
    public static List readExcel(MultipartFile excel, Class clazz) throws IOException {
        List list = readExcel(excel, clazz, 1, 1);
        return JSON.parseArray(JSON.toJSONString(list), clazz);
    }

    /**
     * 读取某个 sheet 的 Excel
     * @param excel       文件
     * @param clazz    实体类映射，继承 BaseRowModel 类
     * @param sheetNo     sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public static List readExcel(MultipartFile excel, Class clazz, int sheetNo, int headLineNum) throws IOException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        reader.read(new Sheet(sheetNo, headLineNum, clazz));
        return excelListener.getDatas();
    }

    /**
     * 读取Excel-动态表头
     * @Author lililiang
     * @Date 2021/8/4 15:29
     */
    public static List readExcel(MultipartFile excel, int sheetNo, int headLineNum) throws IOException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        Sheet sheet = new Sheet(sheetNo, headLineNum);
        reader.read(sheet);
        return excelListener.getDatas();
    }


    /**
     * 读取指定sheetName的Excel(多个 sheet)
     * @param excel    文件
     * @param rowModel 实体类映射，继承 BaseRowModel 类
     * @return Excel 数据 list
     * @throws IOException
     */
    public static List readExcel(MultipartFile excel, BaseRowModel rowModel, String sheetName) throws IOException {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);
        if (reader == null) {
            return null;
        }
        for (Sheet sheet : reader.getSheets()) {
            if (rowModel != null) {
                sheet.setClazz(rowModel.getClass());
            }
            //读取指定名称的sheet
            if(sheet.getSheetName().contains(sheetName)){
                reader.read(sheet);
                break;
            }
        }
        return excelListener.getDatas();
    }

    /**
     * 返回 ExcelReader
     * @param excel 需要解析的 Excel 文件
     * @param excelListener new ExcelListener()
     * @throws IOException
     */
    private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) throws IOException {
        String filename = excel.getOriginalFilename();
        if(filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))){
            InputStream is = new BufferedInputStream(excel.getInputStream());
            return new ExcelReader(is, null, excelListener, false);
        }else{
            return null;
        }
    }

    /**
     * 写excel直接返回到页面
     * @Author lililiang
     * @Date 2021/6/18 10:53
     */
    public static void wirteExcel(HttpServletResponse response, String fileName, String sheetName, List list, Class clazz) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("UTF-8");
        fileName = fileName + ".xlsx";
        response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
        response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1"));
        EasyExcel.write(response.getOutputStream(), clazz)
                .registerWriteHandler(new CustomCellWriteHandler())
                .registerWriteHandler(getHorizontalCellStyleStrategy())
                .sheet(sheetName)
                .doWrite(list);
    }

    /**
     * 写excel直接返回到页面,设置合并单元格
     * @Author lililiang
     * @Date 2021/6/18 10:53
     */
    public static void wirteExcel(HttpServletResponse response, String fileName, String sheetName, List list, Class clazz, int startRowIndex, List<Integer> startColIndexs) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("UTF-8");
        fileName = fileName + ".xlsx";
        response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
        response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1"));
        EasyExcel.write(response.getOutputStream(), clazz)
                .registerWriteHandler(new CustomCellWriteHandler())
                .registerWriteHandler(getHorizontalCellStyleStrategy())
                .registerWriteHandler(new CellMergeHandler(startRowIndex, startColIndexs))
                .sheet(sheetName)
                .doWrite(list);
    }

    /**
     * 写excel直接返回到页面,设置动态头
     * @Author lililiang
     * @Date 2021/6/18 10:53
     */
    public static void wirteExcel(HttpServletResponse response, String fileName, String sheetName, List list, List<List<String>> head) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("UTF-8");
        fileName = fileName + ".xlsx";
        response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
        response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1"));

        ExcelWriter writer = EasyExcelFactory.getWriter(response.getOutputStream());
        Sheet sheet = new Sheet(1,0);
        sheet.setSheetName(sheetName);
        Table table = new Table(1);
        table.setHead(head);
        writer.write(list, sheet, table);
        writer.finish();
    }


    /**
     * 定义excel样式
     * @Author lililiang
     * @Date 2021/7/6 14:57
     */
    private static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy(){
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        //设置头字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)13);
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }



}
